use OcOnline
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--ADD STORE PROCEDURE
--ADMIN ADD MANAGER
IF OBJECT_ID ( 'A_AddManager', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_AddManager;
GO
CREATE PROCEDURE A_AddManager
@hoten varchar(50),@diachi varchar(100),@cmnd varchar(20),@nghenghiep varchar(50),@taikhoan varchar(50),@email varchar(50),@quocgia varchar(50),@dtdd varchar(20),@username varchar(20)
AS
BEGIN
INSERT INTO dbo.NGUOI_DUNG(HoTen,DiaChi,CMND,NgheNghiep,TaiKhoan,Email,QuocGia,DTDD,Username) values(@hoten,@diachi,@cmnd,@nghenghiep,@taikhoan,@email,@quocgia,@dtdd,@username)
END
GO
--ADMIN ADD USER
IF OBJECT_ID ( 'A_AddTaiKhoan', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_AddTaiKhoan;
GO

CREATE PROCEDURE A_AddTaiKhoan
@username varchar(20),@password varchar(20),@permission int,@status int
AS
BEGIN
INSERT INTO dbo.TAI_KHOAN(Username,Password,Permission,Status) values(@username,@password,@permission,@status)
END
GO
--END
--ADMIN ADD MONOC
IF OBJECT_ID ( 'A_AddMonoc', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_AddMonoc;
GO
CREATE PROCEDURE A_AddMonoc
@tenmo varchar(100),@chitietmo varchar(10)
AS
BEGIN
INSERT INTO dbo.MON_OC(TenMO,ChiTietMO) values(@tenmo,@chitietmo)
END
GO
--ADMIN ADD DONHANG
IF OBJECT_ID ( 'A_AddDonHang', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_AddDonHang;
GO
CREATE PROCEDURE A_AddDonHang
@username varchar(20),@mamonoc varchar(10),@ngaydat datetime,@soluong int,@giaohang bit
AS
BEGIN
INSERT INTO dbo.DAT_HANG(Username,MaMonOC,DatNgay,SoLuong,GiaoHang) values(@username,@mamonoc,@ngaydat,@soluong,@giaohang)
END
GO
--ADMIN ADD THONGTINMONOC
IF OBJECT_ID ( 'A_AddThongTinMO', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_AddThongTinMO;
GO
CREATE PROCEDURE A_AddThongTinMO
@machitietmo varchar(10),@ngaynhap datetime,@soluongnhap decimal(18,3),@soluongton decimal(18,3),@image varchar(50),@giatien int
AS
BEGIN
INSERT INTO dbo.THONG_TIN_MON_OC(TenCheBien,NgayNhap,TinhTrang,SoLuongNhap,SoLuongTon,Image) VALUES(@machitietmo,@ngaynhap,@soluongnhap,@soluongton,@image,@giatien)
END
GO
--MANAGER

--MANAGER ADD TAIKHOAN
IF OBJECT_ID ( 'M_AddTaiKhoan', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_AddTaiKhoan;
GO

CREATE PROCEDURE M_AddTaiKhoan
@username varchar(20),@password varchar(20),@permission int,@status int
AS
BEGIN
INSERT INTO dbo.TAI_KHOAN(Username,Password,Permission,Status) values(@username,@password,@permission,@status)
END
GO
--END
--MANAGER ADD MONOC
IF OBJECT_ID ( 'M_AddMonoc', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_AddMonoc;
GO
CREATE PROCEDURE M_AddMonoc
@tenmo varchar(100),@chitietmo varchar(10)
AS
BEGIN
INSERT INTO dbo.MON_OC(TenMO,ChiTietMO) values(@tenmo,@chitietmo)
END
GO
--MANAGER ADD DONHANG
IF OBJECT_ID ( 'M_AddDonHang', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_AddDonHang;
GO
CREATE PROCEDURE M_AddDonHang
@username varchar(20),@mamonoc varchar(10),@ngaydat datetime,@soluong int,@giaohang bit
AS
BEGIN
INSERT INTO dbo.DAT_HANG(Username,MaMonOC,DatNgay,SoLuong,GiaoHang) values(@username,@mamonoc,@ngaydat,@soluong,@giaohang)
END
GO
--MANAGER ADD THONGTINMONOC
IF OBJECT_ID ( 'M_AddThongTinMO', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_AddThongTinMO;
GO
CREATE PROCEDURE M_AddThongTinMO
@machitietmo varchar(10),@ngaynhap datetime,@soluongnhap decimal(18,3),@soluongton decimal(18,3),@image varchar(50),@giatien int
AS
BEGIN
INSERT INTO dbo.THONG_TIN_MON_OC(MaChiTietMO,NgayNhap,TinhTrang,SoLuongNhap,SoLuongTon,Image) VALUES(@machitietmo,@ngaynhap,@soluongnhap,@soluongton,@image,@giatien)
END
GO
--USER
--USER ADD DONHANG
IF OBJECT_ID ( 'U_AddDonHang', 'P' ) IS NOT NULL 
    DROP PROCEDURE U_AddDonHang;
GO
CREATE PROCEDURE U_AddDonHang
@username varchar(20),@mamonoc varchar(10),@ngaydat datetime,@soluong int,@giaohang bit
AS
BEGIN
INSERT INTO dbo.DAT_HANG(Username,MaMonOC,DatNgay,SoLuong,GiaoHang) values(@username,@mamonoc,@ngaydat,@soluong,@giaohang)
END
GO


--------------------- UPDATE-----------------------
use OcOnline
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--UPDATE STORE PROCEDURE
--ADMIN UPDATE ADMIN
IF OBJECT_ID ( 'A_UpdateAdmin', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_UpdateAdmin;
GO
CREATE PROCEDURE A_UpdateAdmin
@hoten varchar(50),@diachi varchar(100),@cmnd varchar(20),@nghenghiep varchar(50),@taikhoan varchar(50),@email varchar(50),@quocgia varchar(50),@dtdd varchar(20),@username varchar(20)
AS
BEGIN
UPDATE dbo.NGUOI_DUNG
SET 
HoTen=@hoten,
DiaChi=@diachi,
CMND=@cmnd,
NgheNghiep=@nghenghiep,
TaiKhoan=@taikhoan,
Email=@email,
QuocGia=@quocgia,
DTDD=@dtdd,
Username=@username
END
GO
--ADMIN UPDATE MANAGER
IF OBJECT_ID ( 'A_UPDATEManager', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_UPDATEManager;
GO
CREATE PROCEDURE A_UPDATEManager
@hoten varchar(50),@diachi varchar(100),@cmnd varchar(20),@nghenghiep varchar(50),@taikhoan varchar(50),@email varchar(50),@quocgia varchar(50),@dtdd varchar(20),@username varchar(20)
AS
BEGIN
UPDATE dbo.NGUOI_DUNG
SET 
HoTen=@hoten,
DiaChi=@diachi,
CMND=@cmnd,
NgheNghiep=@nghenghiep,
TaiKhoan=@taikhoan,
Email=@email,
QuocGia=@quocgia,
DTDD=@dtdd,
Username=@username
END
GO
--ADMIN UPDATE USER
IF OBJECT_ID ( 'A_UpdateUser', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_UpdateUser;
GO
CREATE PROCEDURE A_UpdateUser
@hoten varchar(50),@diachi varchar(100),@cmnd varchar(20),@nghenghiep varchar(50),@taikhoan varchar(50),@email varchar(50),@quocgia varchar(50),@dtdd varchar(20),@username varchar(20)
AS
BEGIN
UPDATE dbo.NGUOI_DUNG
SET 
HoTen=@hoten,
DiaChi=@diachi,
CMND=@cmnd,
NgheNghiep=@nghenghiep,
TaiKhoan=@taikhoan,
Email=@email,
QuocGia=@quocgia,
DTDD=@dtdd,
Username=@username
END
GO
--END
--ADMIN UPDATE MONOC
IF OBJECT_ID ( 'A_UPDATEMonoc', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_UPDATEMonoc;
GO
CREATE PROCEDURE A_UPDATEMonoc
@tenmo varchar(100),@chitietmo varchar(10)
AS
BEGIN
UPDATE dbo.MON_OC
SET 
TenMO = @tenmo,
ChiTietMO = @chitietmo
END
GO
--ADMIN UPDATE DONHANG
IF OBJECT_ID ( 'A_UPDATEDonHang', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_UPDATEDonHang;
GO
CREATE PROCEDURE A_UPDATEDonHang
@username varchar(20),@mamonoc varchar(10),@ngaydat datetime,@soluong int,@giaohang bit
AS
BEGIN
UPDATE dbo.DAT_HANG
SET
Username=@username,
MaMonOC=@mamonoc,
DatNgay=@ngaydat,
SoLuong=@soluong,
GiaoHang= @giaohang
END
GO
--ADMIN UPDATE THONGTINMONOC
IF OBJECT_ID ( 'A_UPDATEThongTinMO', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_UPDATEThongTinMO;
GO
CREATE PROCEDURE A_UPDATEThongTinMO
@machitietmo varchar(10),@ngaynhap datetime,@tinhtrang varchar(10),@soluongnhap decimal(18,3),@soluongton decimal(18,3),@image varchar(50),@giatien int
AS
BEGIN
UPDATE dbo.THONG_TIN_MON_OC
SET
MaChiTietMO=@machitietmo,
NgayNhap=@ngaynhap,
TinhTrang=@tinhtrang,
SoLuongNhap=@soluongnhap,
SoLuongTon=@soluongton,
Image =  @image,
GiaTien = @giatien
END
GO
--MANAGER

--MANAGER UPDATE MANAGER
IF OBJECT_ID ( 'M_UPDATEManager', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_UPDATEManager;
GO
CREATE PROCEDURE M_UPDATEManager
@hoten varchar(50),@diachi varchar(100),@cmnd varchar(20),@nghenghiep varchar(50),@taikhoan varchar(50),@email varchar(50),@quocgia varchar(50),@dtdd varchar(20),@username varchar(20)
AS
BEGIN
UPDATE dbo.NGUOI_DUNG
SET 
HoTen=@hoten,
DiaChi=@diachi,
CMND=@cmnd,
NgheNghiep=@nghenghiep,
TaiKhoan=@taikhoan,
Email=@email,
QuocGia=@quocgia,
DTDD=@dtdd,
Username=@username
END
GO
--MANAGER UPDATE USER
IF OBJECT_ID ( 'M_UpdateUser', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_UpdateUser;
GO
CREATE PROCEDURE M_UpdateUser
@hoten varchar(50),@diachi varchar(100),@cmnd varchar(20),@nghenghiep varchar(50),@taikhoan varchar(50),@email varchar(50),@quocgia varchar(50),@dtdd varchar(20),@username varchar(20)
AS
BEGIN
UPDATE dbo.NGUOI_DUNG
SET 
HoTen=@hoten,
DiaChi=@diachi,
CMND=@cmnd,
NgheNghiep=@nghenghiep,
TaiKhoan=@taikhoan,
Email=@email,
QuocGia=@quocgia,
DTDD=@dtdd,
Username=@username
END
GO
--END
--MANAGER UPDATE MONOC
IF OBJECT_ID ( 'M_UPDATEMonoc', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_UPDATEMonoc;
GO
CREATE PROCEDURE M_UPDATEMonoc
@tenmo varchar(100),@chitietmo varchar(10)
AS
BEGIN
UPDATE dbo.MON_OC
SET 
TenMO = @tenmo,
ChiTietMO = @chitietmo
END
GO
--MANAGER UPDATE DONHANG
IF OBJECT_ID ( 'M_UPDATEDonHang', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_UPDATEDonHang;
GO
CREATE PROCEDURE M_UPDATEDonHang
@username varchar(20),@mamonoc varchar(10),@ngaydat datetime,@soluong int,@giaohang bit
AS
BEGIN
UPDATE dbo.DAT_HANG
SET
Username=@username,
MaMonOC=@mamonoc,
DatNgay=@ngaydat,
SoLuong=@soluong,
GiaoHang= @giaohang
END
GO
--MANAGER UPDATE THONGTINMONOC
IF OBJECT_ID ( 'M_UPDATEThongTinMO', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_UPDATEThongTinMO;
GO
CREATE PROCEDURE M_UPDATEThongTinMO
@machitietmo varchar(10),@ngaynhap datetime,@tinhtrang varchar(10),@soluongnhap decimal(18,3),@soluongton decimal(18,3),@image varchar(50),@giatien int
AS
BEGIN
UPDATE dbo.THONG_TIN_MON_OC
SET
MaChiTietMO=@machitietmo,
NgayNhap=@ngaynhap,
TinhTrang=@tinhtrang,
SoLuongNhap=@soluongnhap,
SoLuongTon=@soluongton,
Image =  @image,
GiaTien = @giatien
END
GO
--USER
-- USER UPDATE USER
IF OBJECT_ID ( 'U_UpdateUser', 'P' ) IS NOT NULL 
    DROP PROCEDURE U_UpdateUser;
GO
CREATE PROCEDURE U_UpdateUser
@hoten varchar(50),@diachi varchar(100),@cmnd varchar(20),@nghenghiep varchar(50),@taikhoan varchar(50),@email varchar(50),@quocgia varchar(50),@dtdd varchar(20),@username varchar(20)
AS
BEGIN
UPDATE dbo.NGUOI_DUNG
SET 
HoTen=@hoten,
DiaChi=@diachi,
CMND=@cmnd,
NgheNghiep=@nghenghiep,
TaiKhoan=@taikhoan,
Email=@email,
QuocGia=@quocgia,
DTDD=@dtdd,
Username=@username
END
GO
--USER UPDATE DONHANG
IF OBJECT_ID ( 'U_UPDATEDonHang', 'P' ) IS NOT NULL 
    DROP PROCEDURE U_UPDATEDonHang;
GO
CREATE PROCEDURE U_UPDATEDonHang
@username varchar(20),@mamonoc varchar(10),@ngaydat datetime,@soluong int,@giaohang bit
AS
BEGIN
UPDATE dbo.DAT_HANG
SET
Username=@username,
MaMonOC=@mamonoc,
DatNgay=@ngaydat,
SoLuong=@soluong,
GiaoHang= @giaohang
END
GO

--------------------- SHOW-----------------------
use OcOnline
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Show STORE PROCEDURE
--ADMIN Show ADMIN
IF OBJECT_ID ( 'A_ShowAdmin', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_ShowAdmin;
GO
CREATE PROCEDURE A_ShowAdmin
@username varchar(20)
AS
BEGIN
SELECT *
FROM dbo.NGUOI_DUNG,dbo.TAI_KHOAN
WHERE dbo.NGUOI_DUNG.Username = @username
AND dbo.TAI_KHOAN.Permission = 1
AND dbo.NGUOI_DUNG.Username = dbo.TAI_KHOAN.Username
END
GO
--ADMIN Show MANAGER
IF OBJECT_ID ( 'A_ShowManager', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_ShowManager;
GO
CREATE PROCEDURE A_ShowManager
@username varchar(20)
AS
BEGIN
SELECT *
FROM dbo.NGUOI_DUNG,dbo.TAI_KHOAN
WHERE dbo.NGUOI_DUNG.Username = @username
AND dbo.TAI_KHOAN.Permission = 2
AND dbo.NGUOI_DUNG.Username = dbo.TAI_KHOAN.Username
END
GO
--ADMIN Show USER
IF OBJECT_ID ( 'A_ShowUser', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_ShowUser;
GO
CREATE PROCEDURE A_ShowUser
@username varchar(20)
AS
BEGIN
SELECT *
FROM dbo.NGUOI_DUNG,dbo.TAI_KHOAN
WHERE dbo.NGUOI_DUNG.Username = @username
AND dbo.TAI_KHOAN.Permission = 3
AND dbo.NGUOI_DUNG.Username = dbo.TAI_KHOAN.Username
END
GO
--END
--ADMIN Show MONOC
IF OBJECT_ID ( 'A_ShowMonoc', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_ShowMonoc;
GO
CREATE PROCEDURE A_ShowMonoc
@tenmo varchar(100)
AS
BEGIN
SELECT *
FROM dbo.MON_OC
WHERE dbo.MON_OC.TenMO = @tenmo

END
GO
--ADMIN Show DONHANG
IF OBJECT_ID ( 'A_ShowDonHang', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_ShowDonHang;
GO
CREATE PROCEDURE A_ShowDonHang
@id bigint
AS
BEGIN
SELECT *
FROM dbo.DAT_HANG
WHERE dbo.DAT_HANG.ID = @id
END
GO
--ADMIN Show THONGTINMONOC
IF OBJECT_ID ( 'A_ShowThongTinMO', 'P' ) IS NOT NULL 
    DROP PROCEDURE A_ShowThongTinMO;
GO
CREATE PROCEDURE A_ShowThongTinMO
@machitietmo varchar(10)
AS
BEGIN
SELECT *
FROM dbo.THONG_TIN_MON_OC
WHERE dbo.THONG_TIN_MON_OC.MaChiTietMO = @machitietmo
END
GO
--MANAGER

--MANAGER Show MANAGER
--MANAGER Show MANAGER
IF OBJECT_ID ( 'M_ShowManager', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_ShowManager;
GO
CREATE PROCEDURE M_ShowManager
@username varchar(20)
AS
BEGIN
SELECT *
FROM dbo.NGUOI_DUNG,dbo.TAI_KHOAN
WHERE dbo.NGUOI_DUNG.Username = @username
AND dbo.TAI_KHOAN.Permission = 2
AND dbo.NGUOI_DUNG.Username = dbo.TAI_KHOAN.Username
END
GO
--MANAGER Show USER
IF OBJECT_ID ( 'M_ShowUser', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_ShowUser;
GO
CREATE PROCEDURE M_ShowUser
@username varchar(20)
AS
BEGIN
SELECT *
FROM dbo.NGUOI_DUNG,dbo.TAI_KHOAN
WHERE dbo.NGUOI_DUNG.Username = @username
AND dbo.TAI_KHOAN.Permission = 3
AND dbo.NGUOI_DUNG.Username = dbo.TAI_KHOAN.Username
END
GO
--END
--MANAGER Show MONOC
IF OBJECT_ID ( 'M_ShowMonoc', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_ShowMonoc;
GO
CREATE PROCEDURE M_ShowMonoc
@tenmo varchar(100)
AS
BEGIN
SELECT *
FROM dbo.MON_OC
WHERE dbo.MON_OC.TenMO = @tenmo

END
GO
--MANAGER Show DONHANG
IF OBJECT_ID ( 'M_ShowDonHang', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_ShowDonHang;
GO
CREATE PROCEDURE M_ShowDonHang
@id bigint
AS
BEGIN
SELECT *
FROM dbo.DAT_HANG
WHERE dbo.DAT_HANG.ID = @id
END
GO
--MANAGER Show THONGTINMONOC
IF OBJECT_ID ( 'M_ShowThongTinMO', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_ShowThongTinMO;
GO
CREATE PROCEDURE M_ShowThongTinMO
@machitietmo varchar(10)
AS
BEGIN
SELECT *
FROM dbo.THONG_TIN_MON_OC
WHERE dbo.THONG_TIN_MON_OC.MaChiTietMO = @machitietmo
END
GO
--USER
-- USER Show USER
IF OBJECT_ID ( 'M_ShowUser', 'P' ) IS NOT NULL 
    DROP PROCEDURE M_ShowUser;
GO
CREATE PROCEDURE M_ShowUser
@username varchar(20)
AS
BEGIN
SELECT *
FROM dbo.NGUOI_DUNG,dbo.TAI_KHOAN
WHERE dbo.NGUOI_DUNG.Username = @username
AND dbo.TAI_KHOAN.Permission = 3
AND dbo.NGUOI_DUNG.Username = dbo.TAI_KHOAN.Username
END
GO
--END
--USER Show MONOC
IF OBJECT_ID ( 'U_ShowMonoc', 'P' ) IS NOT NULL 
    DROP PROCEDURE U_ShowMonoc;
GO
CREATE PROCEDURE U_ShowMonoc
@tenmo varchar(100)
AS
BEGIN
SELECT *
FROM dbo.MON_OC
WHERE dbo.MON_OC.TenMO = @tenmo

END
GO
--USER Show DONHANG
IF OBJECT_ID ( 'U_ShowDonHang', 'P' ) IS NOT NULL 
    DROP PROCEDURE U_ShowDonHang;
GO
CREATE PROCEDURE U_ShowDonHang
@id bigint
AS
BEGIN
SELECT *
FROM dbo.DAT_HANG
WHERE dbo.DAT_HANG.ID = @id
END
GO
--USER Show THONGTINMONOC
IF OBJECT_ID ( 'U_ShowThongTinMO', 'P' ) IS NOT NULL 
    DROP PROCEDURE U_ShowThongTinMO;
GO
CREATE PROCEDURE U_ShowThongTinMO
@machitietmo varchar(10)
AS
BEGIN
SELECT *
FROM dbo.THONG_TIN_MON_OC
WHERE dbo.THONG_TIN_MON_OC.MaChiTietMO = @machitietmo
END
GO